package com.school.dao.impl;

import com.school.config.DbcpConfig;
import com.school.dao.PermissionDao;
import com.school.model.Permission;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PermissionDaoImpl extends BaseDaoImpl<Permission> implements PermissionDao {

    @Override
    public Permission insert(Permission permission) {
        String sql = "insert into " + tableName + " (url,method,type,note) value (?,?,?,?)";
        try {
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = DbcpConfig.getPreparedStatement(sql);
            if (statement != null) {
                statement.setString(1, permission.getUrl());
                statement.setString(2, permission.getMethod());
                statement.setString(3, permission.getType());
                statement.setString(4, permission.getNote());
                // 执行上面的sql语句取查询，得到查询的条数
                statement.executeUpdate();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                while (keys.next()) {
                    permission.setId(keys.getInt(1));
                }
                // 关闭查询过程
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return permission;
    }

    @Override
    public List<Permission> insert(List<Permission> permissions) {
        String sql = "insert into " + tableName + " (url,method,type,note) value (?,?,?,?)";
        try {
            // 获取连接对象
            Connection connection = DbcpConfig.getConnection();
            // 开启事务
            connection.setAutoCommit(false);
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (statement != null) {
                for (Permission permission : permissions) {
                    statement.setString(1, permission.getUrl());
                    statement.setString(2, permission.getMethod());
                    statement.setString(3, permission.getType());
                    statement.setString(4, permission.getNote());
                    // 正常情况需要提交，但是现在暂存一次操作
                    statement.addBatch();
                }
                // 再一次性全部提交
                statement.executeBatch();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                for (Permission permission : permissions) {
                    keys.next();
                    if (keys != null) {
                        permission.setId(keys.getInt(1));
                    }
                }
                // 关闭查询过程
                statement.close();
                connection.commit();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return permissions;
    }

    @Override
    public int update(Permission permission) {
        // update student set stunum='fjsjklf', sex='女' wehere id=1
        StringBuffer sql = new StringBuffer("update " + tableName + " set ");
        if (permission.getUrl() != null) {
            sql.append(" url = '" + permission.getUrl() + "',");
        }
        if (permission.getMethod() != null) {
            sql.append(" method = '" + permission.getMethod() + "',");
        }
        if (permission.getType() != null) {
            sql.append(" type = '" + permission.getType() + "',");
        }

        if (permission.getNote() != null) {
            sql.append(" note = '" + permission.getNote() + "',");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf(",");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 1);
        sql.append(" where id = " + permission.getId());
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                return preparedStatement.executeUpdate();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int[] update(List<Permission> permissions) {
        try {
            Connection connection = DbcpConfig.getConnection();
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            if (statement != null) {
                for (Permission permission : permissions) {
                    // update student set stunum='fjsjklf', sex='女' wehere id=1
                    StringBuffer sql = new StringBuffer("update " + tableName + " set ");
                    if (permission.getUrl() != null) {
                        sql.append(" url = '" + permission.getUrl() + "',");
                    }
                    if (permission.getMethod() != null) {
                        sql.append(" method = '" + permission.getMethod() + "',");
                    }
                    if (permission.getType() != null) {
                        sql.append(" type = '" + permission.getType() + "',");
                    }

                    if (permission.getNote() != null) {
                        sql.append(" note = '" + permission.getNote() + "',");
                    }
                    // 获取”,“最后一次出现的位置下标
                    int lastIndex = sql.lastIndexOf(",");
                    // 删除最后一个”,“
                    sql.delete(lastIndex, lastIndex + 1);
                    sql.append(" where id = " + permission.getId());
                    System.out.println(sql);
                    statement.addBatch(sql.toString());
                }
                // 返回成功和失败：1-成功；0-失败
                int res[] = statement.executeBatch();
                connection.commit();
                statement.close();
                return res;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new int[0];
    }

    @Override
    public Permission findOneById(Integer id) {
        String sql = "select id,url,method,type,note from " + tableName + " where id = ?";
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql);
            if (preparedStatement != null) {
                preparedStatement.setInt(1, id);
                ResultSet resultSet = preparedStatement.executeQuery();
                Permission permission = new Permission();
                while (resultSet.next()) {
                    permission.setId(resultSet.getInt("id"));
                    permission.setUrl(resultSet.getString("url"));
                    permission.setMethod(resultSet.getString("method"));
                    permission.setType(resultSet.getString("type"));
                    permission.setNote(resultSet.getString("note"));
                }
                preparedStatement.close();
                return permission;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new Permission();
    }

    @Override
    public List<Permission> findPage(Permission permission, Integer offset, Integer size) {
        // select * from student limit 0,10;
        StringBuffer sql = new StringBuffer("select id,url,method,type,note from ");
        sql.append(tableName).append(" where ");
        if (permission.getUrl() != null) {
            sql.append(" url like '%" + permission.getUrl() + "%' or ");
        }
        if (permission.getMethod() != null) {
            sql.append(" method like '%" + permission.getMethod() + "%' or ");
        }
        if (permission.getType() != null) {
            sql.append(" type like '%" + permission.getType() + "%' or ");
        }
        if (permission.getNote() != null) {
            sql.append(" note like '%" + permission.getNote() + "%' or ");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf("or");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 2);
        sql.append(" limit ?,?");
        System.out.println(sql.toString());
        List<Permission> list = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, size);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    Permission permission1 = new Permission();
                    permission1.setId(resultSet.getInt("id"));
                    permission1.setUrl(resultSet.getString("url"));
                    permission1.setMethod(resultSet.getString("method"));
                    permission1.setType(resultSet.getString("type"));
                    permission1.setNote(resultSet.getString("note"));
                    list.add(permission1);
                }
                preparedStatement.close();
                return list;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
}
